﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace Taobao.DBServer
{
    public delegate void OnTableRowUpdating(object sender, SqlRowUpdatingEventArgs e);
    public delegate void OnTableRowUpdated(object sender, SqlRowUpdatedEventArgs e);
    public class DBServer : IDisposable
    {

        #region 变量
        private static string _ServerAddress;
        private static string _DataBase;
        private static string _UserID;
        private static string _Password;

        public static string ServerAddress { get { return _ServerAddress; } set { _ServerAddress = value; } }

        public static string DataBase { get { return _DataBase; } set { _DataBase = value; } }

        public static string UserID { get { return _UserID; } set { _UserID = value; } }

        public static string Password { get { return _Password; } set { _Password = value; } }

        private SqlConnection Con;
        private SqlCommand objCmd;
        public static event OnTableRowUpdating OnTableRowUpdating;
        public static event OnTableRowUpdated OnTableRowUpdated;
        #endregion

        /// <summary>
        /// 连接数据库
        /// </summary>
        public DBServer()
        {
            string constr = @"server={0};uid={1};pwd={2};database={3};timeout=60;";
            constr = string.Format(constr, _ServerAddress, _UserID, _Password, _DataBase);
            Con = new SqlConnection(constr);
        }
        /// <summary>
        /// 打开数据库
        /// </summary>
        public bool Open()
        {
            bool ret = false;
            try
            {
                if (Con.State != System.Data.ConnectionState.Open)
                {
                    Con.Close();
                    Con.Open();
                }

                ret = true;
            }
            catch (System.Data.DataException ex)
            {
                ret = false;
                WriteLine("Unable to connect remote database.\n" + ex.Message);
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                WriteLine("Unable to connect remote database.\n" + ex.Message);
            }
            catch (System.IO.IOException ex)
            {
                ret = false;
                WriteLine("Unable to connect remote database.\n" + ex.Message);
            }
            return ret;
        }
        /// <summary>
        /// 关闭数据库
        /// </summary>
        public bool Close()
        {
            try
            {
                if (Con.State != System.Data.ConnectionState.Closed)
                {
                    Con.Close();
                }
                return true;
            }
            catch { return false; }
        }

        private SqlTransaction _transaction = null;

        /// <summary>
        /// 启动一个事务
        /// </summary>
        public void BeginTransaction()
        {
            _transaction = Con.BeginTransaction();
        }

        /// <summary>
        /// 提交当前事务
        /// </summary>
        public void CommitTransaction()
        {
            if (_transaction != null && _transaction.Connection != null)
            {
                try
                {
                    _transaction.Commit();
                    _transaction.Dispose();
                    _transaction = null;
                }
                catch (System.Data.DataException ex)
                {
                    _transaction.Rollback();
                    throw ex;
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    _transaction.Rollback();
                    throw ex;
                }
                catch (System.IO.IOException ex)
                {
                    _transaction.Rollback();
                    throw ex;
                }
            }
            else
            {
                throw new Exception("Transaction is Null or Transaction.Connection is Null");
            }
        }

        /// <summary>
        /// 回滚当前事务
        /// </summary>
        public void RollbackTransaction()
        {
            if (_transaction != null)
            {
                _transaction.Rollback();
                _transaction.Dispose();
                _transaction = null;
            }
        }

        public int ExecSqlNonQueryTransaction(string Sql)
        {
            try
            {
                objCmd = new SqlCommand(Sql, Con, _transaction);
                return objCmd.ExecuteNonQuery();
            }
            catch (System.Data.DataException ex)
            {
                WriteError("SQL语句： " + Sql + System.Environment.NewLine + "错误：" + ex.Message);
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                WriteError("SQL语句： " + Sql + System.Environment.NewLine + "错误：" + ex.Message);
            }
            catch (System.IO.IOException ex)
            {
                WriteError("SQL语句： " + Sql + System.Environment.NewLine + "错误：" + ex.Message);
            }
            return -1;
        }

        ///// <summary>
        ///// 执行带参数的Sql语句，返回影响记录条数。
        ///// </summary>
        ///// <param name="sql"></param>
        ///// <param name="param"></param>
        ///// <returns></returns>
        //public int ExecParameredNonQuery(string sql, SqlParameter[] param)
        //{
        //    if (!this.Open())
        //        return null;

        //    try
        //    {
        //        SqlCommand cmd = new SqlCommand(sql, Con);
        //        cmd.Parameters.AddRange(param);
        //        return cmd.ExecuteNonQuery();
        //    }
        //    finally
        //    {
        //        Con.Close();
        //    }
        //}

        /// <summary>
        /// 执行存储过程并返回纪录集
        /// </summary>
        /// <param name="ProcedureName">存储过程名</param>
        /// <param name="param">参数集合</param>
        /// <returns>纪录集</returns>
        public SqlDataReader ExecProcReader(string ProcedureName, SqlParameter[] param)
        {
            if (!this.Open())
                return null;

            SqlDataReader sdr = null;
            try
            {
                objCmd = new SqlCommand();
                objCmd.Connection = this.Con;
                objCmd.CommandText = ProcedureName;
                objCmd.CommandType = CommandType.StoredProcedure;
                if (param != null)
                    objCmd.Parameters.AddRange(param);
                return objCmd.ExecuteReader();
            }
            catch (System.Data.DataException ex)
            {
                WriteLine("[DBServer.ExecProcReader]: " + ex.Message);
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                WriteLine("[DBServer.ExecProcReader]: " + ex.Message);
            }
            catch (System.IO.IOException ex)
            {
                WriteLine("[DBServer.ExecProcReader]: " + ex.Message);
            }
            finally
            {
                //this.Close();
            }
            return sdr;
        }

        /// <summary>
        /// 执行存储过程并返回纪录集
        /// </summary>
        /// <param name="ProcedureName">存储过程名</param>
        /// <param name="param">参数集合</param>
        /// <returns>纪录集</returns>
        public DataTable ExecProc(string ProcedureName, SqlParameter[] param)
        {
            if (!this.Open())
                return null;
            DataTable dt = new DataTable();
            SqlDataReader sdr = null;
            try
            {
                objCmd = new SqlCommand();
                objCmd.Connection = this.Con;
                objCmd.CommandText = ProcedureName;
                objCmd.CommandType = CommandType.StoredProcedure;
                objCmd.Parameters.AddRange(param);
                sdr = objCmd.ExecuteReader();
                dt.Load(sdr);
                sdr.Close();
            }
            catch (System.Data.DataException ex)
            {
                WriteLine("[DBServer.ExecProc]: " + ex.Message);
                dt = null;
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                WriteLine("[DBServer.ExecProc]: " + ex.Message);
                dt = null;
            }
            catch (System.IO.IOException ex)
            {
                WriteLine("[DBServer.ExecProc]: " + ex.Message);
                dt = null;
            }
            finally
            {
                if (sdr != null)
                    sdr.Close();
                objCmd = null;
                this.Close();
            }
            return dt;
        }

        /// <summary>
        /// 执行存储过程并返回影响的行数
        /// </summary>
        /// <param name="ProcedureName">存储过程名</param>
        /// <param name="param">参数集合</param>
        /// <returns>返回执行存储过程后影响的行数</returns>
        public int ExecProcNonQuery(string ProcedureName, SqlParameter[] param)
        {
            if (!this.Open())
                return 0;
            int cnt = 0;
            try
            {
                objCmd = new SqlCommand();
                objCmd.Connection = this.Con;
                objCmd.CommandText = ProcedureName;
                objCmd.CommandType = CommandType.StoredProcedure;
                objCmd.Parameters.AddRange(param);
                cnt = objCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("[DBServer.ExecProcNonQuery][proc=> " + ProcedureName + "]: " + ex.Message);
                cnt = 0;
            }
            finally
            {
                objCmd = null;
                this.Close();
            }
            return cnt;
        }

        /// <summary>
        /// 执行SQL语句，返回数据集。(使用SqlDataAdapter)
        /// </summary>
        public DataSet ReturnDataSet(string Sql)
        {
            if (!this.Open())
                return null;
            DataSet ds = new DataSet();
            SqlDataAdapter objAdapter = new SqlDataAdapter(Sql, Con);
            try
            {
                objAdapter.Fill(ds);
            }
            catch (Exception ex)
            {
                WriteLine("[DBServer.ReturnDataSet][sql=> " + Sql + "]: " + ex.Message);
                ds = null;
            }
            finally
            {
                objAdapter.Dispose();
                this.Close();
            }
            return ds;
        }

        /// <summary>
        /// 执行SQL语句，返回数据表。(使用ExecuteReader)
        /// </summary>
        public DataTable ReturnDataTable(string Sql)
        {
            if (!Open())
                return null;
            DataTable dt = new DataTable();
            SqlDataReader sdr = null;
            objCmd = new SqlCommand(Sql, Con);
            //int error = 0;
            try
            {
                sdr = objCmd.ExecuteReader();
                dt.Load(sdr);
                //DataSet ds = new DataSet();
                //SqlDataAdapter objAdapter = new SqlDataAdapter(Sql, Con);
                //objAdapter.Fill(ds);
                //if (ds.Tables.Count > 0)
                //    dt = ds.Tables[0];
            }
            //catch(SqlException ex) { 
            //    dt = null;
            //    Console.WriteLine("[DBServer.ReturnDataSet][sql=> " + Sql + "]: " + ex.Message);
            //}
            finally
            {
                if (sdr != null)
                    sdr.Close();
                objCmd = null;
                this.Close();
            }
            return dt;
        }

        /// <summary>
        /// 执行SQL语句，返回数据表。(使用ExecuteReader)
        /// </summary>
        public DataTable ReturnDataTable(string Sql, out int ErrorNumber)
        {
            if (!Open())
            {
                ErrorNumber = 0;
                return null;
            }
            DataTable dt = new DataTable();
            SqlDataReader sdr = null;
            objCmd = new SqlCommand(Sql, Con);
            int error = 0;
            try
            {
                sdr = objCmd.ExecuteReader();
                dt.Load(sdr);
                //DataSet ds = new DataSet();
                //SqlDataAdapter objAdapter = new SqlDataAdapter(Sql, Con);
                //objAdapter.Fill(ds);
                //if (ds.Tables.Count > 0)
                //    dt = ds.Tables[0];
            }
            catch (SqlException ex)
            {
                dt = null;
                Console.WriteLine("[DBServer.ReturnDataSet][sql=> " + Sql + "]: " + ex.Message);
                Console.WriteLine("[DBServer.ReturnDataSet]ErrorNumber: " + ex.Number);//-2
                Console.WriteLine("[DBServer.ReturnDataSet]ErrorCode: " + ex.ErrorCode);//-2146232060
                error = ex.Number;
            }
            finally
            {
                if (sdr != null)
                    sdr.Close();
                objCmd = null;
                this.Close();
                ErrorNumber = error;
            }
            return dt;
        }

        /// <summary>
        /// 执行SQL，返回是否执行成功
        /// </summary>
        public bool ExecSql(string Sql)
        {
            //http://hi.baidu.com/%B9%BC%BF%AD/blog/item/dea28aefbeb2cd37acafd5a9.html
            if (!this.Open())
                return false;
            int cnt = 0;
            try
            {
                objCmd = new SqlCommand(Sql, Con);
                cnt = objCmd.ExecuteNonQuery();
                //0x001E3000
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                //throw e;
                Console.WriteLine("[DBServer.ExecSql][sql=> " + Sql + "]: " + ex.Message);
                cnt = 0;
            }
            finally { objCmd = null; this.Close(); }
            if (cnt != 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 执行SQL，返回影响行数(所需数据库连接需先自行打开和关闭)
        /// </summary>
        public int ExecSqlNonQuery(string Sql)
        {
            int cnt = 0;
            try
            {
                if (this.Con.State == ConnectionState.Closed)
                {
                    Con.Open();
                }
                objCmd = new SqlCommand(Sql, Con);
                cnt = objCmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {

                cnt = 0;
                //System.Diagnostics.Debug.WriteLine("SQL语句： " + Sql + System.Environment.NewLine + "错误：" + e.Message);
                //this.Close();
                WriteError("SQL语句： " + Sql + System.Environment.NewLine + "错误：" + e.Message);
            }
            return cnt;
        }


        /// <summary>
        /// 执行SQL语句，判断数据是否存在
        /// </summary>
        public bool RetrunIsExist(string Sql)
        {

            bool ret = false;
            if (!this.Open())
                return false;
            try
            {

                objCmd = new SqlCommand(Sql, Con);
                object o = objCmd.ExecuteScalar();
                if (o != null)
                {
                    ret = true;
                }
                else
                {
                    ret = false;
                }

            }
            catch { ret = false; }
            finally { this.Close(); }
            return ret;
        }

        /// <summary>
        /// 执行SQL语句，返回相关的数据信息
        /// </summary>
        public string ReturnExecSql(string sql)
        {
            string ret = null;
            if (!this.Open())
                return ret;
            try
            {
                objCmd = new SqlCommand(sql, Con);
                object o = objCmd.ExecuteScalar();
                if (o != null)
                {
                    ret = o.ToString();
                }
                else
                {
                    ret = null;
                }
                this.Close();
            }
            catch { ret = null; }
            finally { this.Close(); objCmd = null; }
            return ret;
        }
        public string MGAMEReturnExecSql(string sql)
        {
            string ret = null;
            if (!this.Open())
                return ret;
            try
            {
                objCmd = new SqlCommand(sql, Con);
                object o = objCmd.ExecuteScalar();
                if (o != null)
                {
                    ret = o.ToString();
                }
                else
                {
                    ret = null;
                }
                this.Close();
            }
            catch { ret = "-1"; }
            finally { this.Close(); objCmd = null; }
            return ret;
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="ProcedureName">存储过程名</param>
        /// <param name="ParamValues">参数值集合</param>
        public void ExecProc(string ProcedureName, params object[] ParamValues)
        {
            try
            {
                if (!this.Open())
                    return;
                objCmd = new SqlCommand();
                objCmd.Connection = Con;
                objCmd.CommandText = ProcedureName;
                objCmd.CommandType = CommandType.StoredProcedure;
                if (ParamValues != null)
                {
                    SqlCommandBuilder.DeriveParameters(objCmd);
                    if (objCmd.Parameters[0].Direction == ParameterDirection.ReturnValue)
                        objCmd.Parameters.RemoveAt(0);
                    for (int i = 0; i < objCmd.Parameters.Count; i++)
                        objCmd.Parameters[i].Value = DBNull.Value;
                    for (int i = 0; i < objCmd.Parameters.Count; i++)
                    {
                        objCmd.Parameters[i].Value = ParamValues[i];
                    }
                }
                objCmd.ExecuteNonQuery();
                this.Close();
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            finally { this.Close(); }
        }

        /// <summary>
        /// 更新数据集到指定表中.[说明:使用该方法,表结构中必须要有主键字段]
        /// </summary>
        /// <param name="tableName">指定表名</param>
        /// <param name="dt">DataTable表数据或预删除的表数据</param>
        /// <param name="dt">是否为删除操作</param>
        public void Update(string tableName, DataTable dt, bool Isdelete)
        {
            if (dt != null && dt.Rows.Count > 0 && !string.IsNullOrEmpty(tableName))
            {
                try
                {

                    SqlCommand selectcmd = new SqlCommand("select * from " + tableName, this.Con);
                    SqlDataAdapter da = new SqlDataAdapter(selectcmd);
                    DataTable olddt = new DataTable();
                    da.Fill(olddt);
                    //string colname = dt.PrimaryKey[0].ColumnName;
                    //olddt.PrimaryKey = new DataColumn[] { olddt.Columns[2], olddt.Columns[3] };
                    DataColumn[] primaryKeys = new DataColumn[dt.PrimaryKey.Length];
                    for (int i = 0; i < primaryKeys.Length; i++)
                    {
                        primaryKeys[i] = olddt.Columns[dt.PrimaryKey[i].ColumnName];
                    }
                    da.ContinueUpdateOnError = false;
                    SqlCommandBuilder builder = new SqlCommandBuilder(da);
                    da.SelectCommand = selectcmd;
                    da.InsertCommand = builder.GetInsertCommand();
                    da.UpdateCommand = builder.GetUpdateCommand();
                    da.DeleteCommand = builder.GetDeleteCommand();
                    da.RowUpdated += new SqlRowUpdatedEventHandler(da_RowUpdated);
                    da.RowUpdating += new SqlRowUpdatingEventHandler(da_RowUpdating);
                    DataTable changetb;
                    if (!Isdelete)
                    {
                        olddt.Merge(dt, false, MissingSchemaAction.AddWithKey);
                        changetb = olddt.GetChanges();
                        da.Update(changetb);
                        olddt.AcceptChanges();
                    }
                    else
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            object[] keys = new object[primaryKeys.Length];
                            for (int j = 0; j < primaryKeys.Length; j++)
                                keys[j] = dt.Rows[i][primaryKeys[j].ColumnName];
                            DataRow delrow = olddt.Rows.Find(keys);
                            delrow.Delete();
                        }
                        da.Update(olddt);
                    }
                    olddt.AcceptChanges();

                }
                catch (SqlException ex) { Console.WriteLine("Error - [DBServer.Update]:" + ex.Message); }
                finally
                {
                    Close();
                }
            }
        }

        void da_RowUpdating(object sender, SqlRowUpdatingEventArgs e)
        {
            if (OnTableRowUpdating != null)
                OnTableRowUpdating(sender, e);
        }

        void da_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
        {
            if (OnTableRowUpdated != null)
                OnTableRowUpdated(sender, e);
        }

        private void WriteError(string msg)
        {
            msg = msg.Replace(";", ";\r\n");

            string path = Directory.GetCurrentDirectory() + Path.DirectorySeparatorChar + "SPError";
            string name = DateTime.Now.Date.ToString("yyyyMMdd") + ".txt";

            if (!Directory.Exists(path))
                Directory.CreateDirectory(path);

            FileStream fs = null;
            StreamWriter sw = null;
            try
            {
                fs = File.Open(path + Path.DirectorySeparatorChar + name, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite);
                fs.Seek(0, SeekOrigin.End);
                sw = new StreamWriter(fs);
                sw.WriteLine(msg);
                sw.WriteLine();
            }
            catch { }
            finally
            {
                if (sw != null)
                    sw.Close();
                if (fs != null)
                    fs.Close();
            }
        }

        #region IDisposable 成员

        public void Dispose()
        {
            if (Con != null)
            {
                if (Con.State != ConnectionState.Closed)
                    Con.Close();
                Con.Dispose();
                Con = null;
            }
            GC.SuppressFinalize(this);
            if (_transaction != null)
            {
                _transaction.Dispose();
                _transaction = null;
            }
        }

        #endregion

        protected virtual void WriteLine(string msg)
        {
            Console.WriteLine(msg);
        }
    }
}

